﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetDistribution_CreateMeterByXml')
	DROP PROCEDURE up_UtilityAssetDistribution_CreateMeterByXml

GO

CREATE PROCEDURE [dbo].[up_UtilityAssetDistribution_CreateMeterByXml]
(
	@vendorProjectId int,
	@xml			xml
) 
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

PRINT 'Prepare table used to update/insert assets'

declare @assetsToCreate as table (DistributionMeterId int, DistributionAssetId int)

INSERT INTO @assetsToCreate
(
	DistributionMeterId,
	DistributionAssetId
)
SELECT 
	Item.query('.').value('(VisitToAsset/Visit)[1]', 'int') as VisitId,
	CASE 
		WHEN Item.query('.').value('(VisitToAsset/Asset)[1]', 'int') = 0 THEN NULL
		ELSE Item.query('.').value('(VisitToAsset/Asset)[1]', 'int')
	END as AssetId
FROM
	@xml.nodes('/VisitsToAssets/VisitToAsset') T(Item) 

PRINT 'Update Asset Fk from Distribution meter inspections'

UPDATE 
	tb_Vendor_Distribution_Meters
SET
	Utility_Asset_Meters_MeterFk = DistributionAssetId
FROM 
	@assetsToCreate
WHERE
	Vendor_Distribution_Meters_MeterId = DistributionMeterId

PRINT 'Add Assets from visits'

DECLARE @insertedAssetIds TABLE (Utility_Asset_MeterId int, MeterId int)

--Inserts asset meters
INSERT INTO tb_Utility_Asset_Meters
(
	UtilityFK,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	HandHeld,
	Meter_TopTypeFk,
	Utility_ContactFk,
	Date,
	Utility_ProjectFk,
	MapFk,
	SectionFk,
	Number,
	Comments
)
OUTPUT INSERTED.Utility_Asset_MeterId, INSERTED.Altitude INTO @insertedAssetIds
SELECT
	@utilityId,
	meters.Latitude,
	meters.Longitude,
	meters.Vendor_Distribution_Meters_MeterId, -- TODO: Change this, Using Altitude as temp id
	NULL, 
	NULL,
	0,
	NULL,
	NULL,
	NULL,
	poles.AcquisitionDate,
	@UtilityProjectId,
	poles.MapFk,
	poles.SectionFk,
	meters.Number,
	meters.Comments
FROM
	tb_Vendor_Distribution_Poles poles
INNER JOIN tb_Vendor_Distribution_Meters meters
	ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE
	poles.ProjectFk = @vendorProjectId
	AND meters.Utility_Asset_Meters_MeterFk IS NULL
	AND meters.Vendor_Distribution_Meters_MeterId in (SELECT DistributionMeterId FROM @assetsToCreate)

--Insert meter images
INSERT INTO tb_Utility_Asset_Meters_Images
(
	Utility_Asset_MeterFK,
	Direction,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	[FileName]
)
SELECT
	[InsertedIds].Utility_Asset_MeterId,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	0,
	meters.ImageFileName
FROM
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Vendor_Distribution_Meters meters
	ON meters.Vendor_Distribution_Meters_MeterId = [InsertedIds].MeterId
WHERE
	meters.ImageFileName IS NOT NULL

--Insert meter notes
INSERT INTO tb_Utility_Asset_Meters_Notes
(
	Utility_Asset_MeterFK,
	Utility_Asset_MeterNoteFK
)
SELECT 
	[assets].Utility_Asset_MeterId,
	notesxMeter.Vendor_Distribution_NoteFk
FROM
	@insertedAssetIds [assets]
INNER JOIN tb_Vendor_Distribution_NotesXMeter notesxMeter
	ON [assets].MeterId = notesxMeter.Vendor_Distribution_MeterFk


--Update inspection meters to relate them to the asset
UPDATE 
	tb_Vendor_Distribution_Meters 
SET
	Utility_Asset_Meters_MeterFk = [InsertedIds].Utility_Asset_MeterId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Distribution_Meters.Vendor_Distribution_Meters_MeterId = [InsertedIds].MeterId


--Update Altitude to NULL
UPDATE 
	tb_Utility_Asset_Meters
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_Meters
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_Meters.Utility_Asset_MeterId = [InsertedIds].Utility_Asset_MeterId

 